********************************************************************************
* Exporting table for multi-country COVID paper (NPL1 Sample)
* last updated: 2020-10-22
* Corey Vernot, Salim Benhachmi
********************************************************************************

	* 1. Cleaning Data

clear all
use "$da/NPL1_HH", clear
tempfile hh
save `hh'

keep hhid today round
merge 1:m hhid today using "$da/NPL1_IND", force nogen

gen wagebus = wage + bus > 0 if !mi(wage) & !mi(bus) //any wage or n.f. business work last two weeks

collapse (max) employed = wagebus , by(hhid today round base_inc) //aggregate ind. employment to hh level

merge 1:1 hhid today using `hh', nogen
bysort hhid round (today): keep if _n == _N //Keep only first observation for each round
bysort hhid (round): keep if _N == 2 //Keep only households with pre and post observations

*generate categorical splits for baseline income
egen high_base_inc = cut(base_inc), group(2)
gen low_base_inc = 1-high_base_inc

*food insecurity
gen reduced_portions = hunger3 > 1 if !mi(hunger3)
gen skipped_meals = hunger4 > 1 if !mi(hunger4)
egen fd_ins = rowmax(reduced_portions skipped_meals)
bysort hhid (round): replace fd_ins = fd_ins[_N]

keep hhid high_base_inc low_base_inc round employed laborInc_hh fd_ins

reshape wide employed laborInc_hh , i(hhid high_base_inc low_base_inc) j(round)

rename (employed3 laborInc_hh3 employed6 laborInc_hh6 high_base_inc ///
	low_base_inc) (pre_empl pre_inc post_empl post_inc abv_mdn_inc blw_mdn_inc)

gen drop_empl = (post_empl == 0 & pre_empl == 1)
gen drop_inc = (post_inc < pre_inc) if post_inc != . & pre_inc != .

********************************************************************************

	* 2. Output

gen all = 1

local subgroups abv_mdn_inc blw_mdn_inc all
local indicators drop_empl drop_inc fd_ins 

foreach subgroup of local subgroups {
	foreach ind of local indicators {	
		sum `ind' if `subgroup' == 1
		loc mean_`ind'_`subgroup'   : di %10.2fc r(mean)
	}
}

sum all if abv_mdn_inc == 1
loc mean_n_abv_mdn_inc:  di %10.0fc r(N)
sum all if blw_mdn_inc == 1
loc mean_n_blw_mdn_inc:  di %10.0fc r(N)
sum all if blw_mdn_inc == 1 | abv_mdn_inc == 1
loc mean_n_all:  di %10.0fc r(N)

clear
set obs 3

local indicators drop_empl drop_inc fd_ins n

foreach ind of local indicators {	
	gen mean_`ind' = ""
}

foreach ind of local indicators {	
	replace mean_`ind' = "`mean_`ind'_all'" in 1
}

foreach ind of local indicators {	
	replace mean_`ind' = "`mean_`ind'_blw_mdn_inc'" in 2
}

foreach ind of local indicators {	
	replace mean_`ind' = "`mean_`ind'_abv_mdn_inc'" in 3
}

gen mean_mkts_clsd = ""
gen mean_hlth_care_denied = ""
gen mean_ngo_gov_sprt = ""

order mean_drop_inc mean_drop_empl mean_mkts_clsd mean_hlth_care_denied ///
	mean_fd_ins mean_ngo_gov_sprt mean_n

export excel using "$dtab/Main Table.xlsx", sheetmodify cell(B44)

********************************************************************************
